package com.moleq.service;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import com.moleq.db.DBError;
import com.moleq.db.DatabaseHelper;
import com.moleq.db.DatabaseSchema;
import com.moleq.dialog.MessageDialog;
import com.moleq.dialog.MessageDialog.OnClickMessageListener;
import com.moleq.models.FreeFuncRpt;

public class FreeFuncRptService {
	private DatabaseHelper DBHelper;
	private MessageDialog mMessageDialog;
	Context context;

	public FreeFuncRptService() {
	}

	public FreeFuncRptService(Context context) {
		this.context = context;
		this.DBHelper = new DatabaseHelper(context);
	}

	public void DeleteAll() {
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		db.execSQL("DELETE FROM " + DatabaseSchema.FreeFuncRpt.SCHEMA_NAME);
	}

	public void DeleteByID(long id) {
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		db.execSQL("DELETE FROM " + DatabaseSchema.FreeFuncRpt.SCHEMA_NAME
				+ " WHERE " + DatabaseSchema.FreeFuncRpt.FF_ID + " = ?",
				new Object[] { id });
	}

	public boolean CheckFuncInList(double func_id, String date, String user_id) {
		SQLiteDatabase db = DBHelper.getWritableDatabase();

		Cursor cursor = db.rawQuery("select ff_func_id from "
				+ DatabaseSchema.FreeFuncRpt.SCHEMA_NAME
				+ " where ff_func_id = " + func_id + " and ff_date = '" + date
				+ "' and ff_user_id = '" + user_id + "'", null);
		if (cursor == null) {
			ShowMessage(DBError.error, mMessageDialog.MESSAGE_BOX_OK,
					mMessageDialog.ICON_ERROR);
			DBError.error = "";
			return false;
		}
		if (cursor.moveToNext()) {
			cursor.close();
			return true;
		}
		cursor.close();
		return false;
	}

	public void InsertOrUpdate(FreeFuncRpt obj) {
		if (CheckFuncInList(obj.getFf_func_id(), obj.getFf_date(),
				obj.getFf_user_id())) {
			this.Update(obj);
		} else {
			this.Insert(obj);
		}
	}

	public void Update(FreeFuncRpt obj) {
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		double amt = obj.getFf_amt();
		if (amt == 0) {
			db.execSQL(
					"Update "
							+ DatabaseSchema.FreeFuncRpt.SCHEMA_NAME
							+ " set ff_date = ?,ff_qty = ff_qty + ?,"
							+ "ff_amt = ff_amt + ?,ff_user_id = ?,update_datetime = strftime('%Y%m%d%H%M%S','now')"
							+ " where ff_func_id = ? and ff_date = ? and ff_user_id = ?",
							new Object[] { obj.getFf_date(), obj.getFf_qty(),
									0, obj.getFf_user_id(),
									obj.getFf_func_id(), obj.getFf_date(),
									obj.getFf_user_id() });
		}else {
			db.execSQL(
					"Update "
							+ DatabaseSchema.FreeFuncRpt.SCHEMA_NAME
							+ " set ff_date = ?,ff_qty = ff_qty + ?,"
							+ "ff_amt = ff_amt + ?,ff_user_id = ?,update_datetime = strftime('%Y%m%d%H%M%S','now')"
							+ " where ff_func_id = ? and ff_date = ? and ff_user_id = ?",
							new Object[] { obj.getFf_date(), obj.getFf_qty(),
									obj.getFf_amt(), obj.getFf_user_id(),
									obj.getFf_func_id(), obj.getFf_date(),
									obj.getFf_user_id() });
		}
	}

	public void Insert(FreeFuncRpt obj) {
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		double amt = obj.getFf_amt();
		if (amt == 0) {
			db.execSQL(
					"INSERT INTO "
							+ DatabaseSchema.FreeFuncRpt.SCHEMA_NAME
							+ " VALUES(?,?,?,?,?,?,?,"
							+ "strftime('%Y%m%d%H%M%S','now'),strftime('%Y%m%d%H%M%S','now'),?)",
					new Object[] { null, obj.getFf_store_id(), obj.getFf_station(),
							obj.getFf_date(), obj.getFf_func_id(), obj.getFf_qty(),
							0, obj.getFf_user_id() });
		}else {
			db.execSQL(
					"INSERT INTO "
							+ DatabaseSchema.FreeFuncRpt.SCHEMA_NAME
							+ " VALUES(?,?,?,?,?,?,?,"
							+ "strftime('%Y%m%d%H%M%S','now'),strftime('%Y%m%d%H%M%S','now'),?)",
					new Object[] { null, obj.getFf_store_id(), obj.getFf_station(),
							obj.getFf_date(), obj.getFf_func_id(), obj.getFf_qty(),
							obj.getFf_amt(), obj.getFf_user_id() });
		}
	}

	public List<FreeFuncRpt> FindAll() {
		List<FreeFuncRpt> obj = new ArrayList<FreeFuncRpt>();
		SQLiteDatabase db = DBHelper.getWritableDatabase();

		Cursor cursor = db.rawQuery("select * from "
				+ DatabaseSchema.FreeFuncRpt.SCHEMA_NAME, null);
		if (cursor == null) {
			ShowMessage(DBError.error, mMessageDialog.MESSAGE_BOX_OK,
					mMessageDialog.ICON_ERROR);
			DBError.error = "";
			return obj;
		}
		while (cursor.moveToNext()) {
			int ff_id = cursor.getInt(cursor
					.getColumnIndex(DatabaseSchema.FreeFuncRpt.FF_ID));
			int ff_store_id = cursor.getInt(cursor
					.getColumnIndex(DatabaseSchema.FreeFuncRpt.FF_STORE_ID));
			int ff_station = cursor.getInt(cursor
					.getColumnIndex(DatabaseSchema.FreeFuncRpt.FF_STATION));
			String ff_date = cursor.getString(cursor
					.getColumnIndex(DatabaseSchema.FreeFuncRpt.FF_DATE));
			int ff_func_id = cursor.getInt(cursor
					.getColumnIndex(DatabaseSchema.FreeFuncRpt.FF_FUNC_ID));
			int ff_qty = cursor.getInt(cursor
					.getColumnIndex(DatabaseSchema.FreeFuncRpt.FF_QTY));
			double ff_amt = cursor.getDouble(cursor
					.getColumnIndex(DatabaseSchema.FreeFuncRpt.FF_AMT));
			String ff_user_id = cursor.getString(cursor
					.getColumnIndex(DatabaseSchema.FreeFuncRpt.FF_USER_ID));

			obj.add(new FreeFuncRpt(ff_id, ff_store_id, ff_station, ff_date,
					ff_func_id, ff_qty, ff_amt, ff_user_id));
		}
		cursor.close();
		return obj;
	}

	public List<FreeFuncRpt> getFreeFuncRptByDate(String Date) {
		List<FreeFuncRpt> list = new ArrayList<FreeFuncRpt>();
		String sql = "select *,sum(ff_qty) from t_free_func_rpt where ff_date = '"
				+ Date + "' group by ff_func_id order by ff_func_id";
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor == null)
			return list;
		while (cursor.moveToNext()) {
			FreeFuncRpt item = new FreeFuncRpt();
			item.setFf_id(cursor.getInt(0));
			item.setFf_store_id(cursor.getInt(1));
			item.setFf_station(cursor.getInt(2));
			item.setFf_date(cursor.getString(3));
			item.setFf_func_id(cursor.getDouble(4));
			item.setFf_qty(cursor.getInt(10));
			item.setFf_amt(cursor.getDouble(6));
			item.setFf_user_id(cursor.getString(7));
			list.add(item);
		}
		cursor.close();
		return list;
	}

	public List<FreeFuncRpt> getFreeFuncRptByDate(String Date, String UserId) {
		List<FreeFuncRpt> list = new ArrayList<FreeFuncRpt>();
		String sql = "select *,sum(ff_qty) from t_free_func_rpt where ff_date = '"
				+ Date
				+ "' and ff_user_id = '"
				+ UserId
				+ "' group by ff_func_id order by ff_func_id";
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor == null)
			return list;
		while (cursor.moveToNext()) {
			FreeFuncRpt item = new FreeFuncRpt();
			item.setFf_id(cursor.getInt(0));
			item.setFf_store_id(cursor.getInt(1));
			item.setFf_station(cursor.getInt(2));
			item.setFf_date(cursor.getString(3));
			item.setFf_func_id(cursor.getDouble(4));
			item.setFf_qty(cursor.getInt(10));
			item.setFf_amt(cursor.getDouble(6));
			item.setFf_user_id(cursor.getString(7));
			list.add(item);
		}
		cursor.close();
		return list;
	}

	public String getFunName(String FunId) {
		String sql = "select ff_name from t_free_functions where ff_id = "
				+ FunId;
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor == null)
			return "";
		if (cursor.moveToNext()) {
			String name = cursor.getString(0);
			cursor.close();
			return name;
		}
		return "";
	}

	public void ShowMessage(String text, int type, int icon) {
		mMessageDialog = new MessageDialog(context);
		mMessageDialog.setStyle(type);
		mMessageDialog.setMessageIcon(icon);
		mMessageDialog.setMessage(text);
		mMessageDialog.setOnClickMessageListener(onClickMessageListener);
		mMessageDialog.show();
	}

	private OnClickMessageListener onClickMessageListener = new OnClickMessageListener() {

		@Override
		public void onClickYes() {
			// TODO Auto-generated method stub

		}

		@Override
		public void onClickNo() {
			// TODO Auto-generated method stub

		}

		@Override
		public void onClickOK() {
			// TODO Auto-generated method stub
			mMessageDialog.dismiss();
		}
	};
}
